import java.sql.*;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 86183
 * Date: 2024-06-03
 * Time: 20:19
 */
public class Tools {
    //查询院部学生
    public static void selectStuByDep(String department)  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，用占位符 ？的方法实现
        String ss = "select * from why1 where 院部=?";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行之前设置？
        ps.setString(1,department);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            System.out.print(rs.getString(1)+"  ");
            System.out.print(rs.getString(2)+"  ");
            System.out.print(rs.getString(3)+"  ");
            System.out.print(rs.getInt(4)+"  ");
            System.out.println(rs.getString(5));
        }
        //关闭连接
        con.close();
    }
    //查询院部学生
    public static void selectStu(String department)  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，也可以用占位符 ？的方法实现
        String ss = "select * from why1 where 院部='"+department+"'";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            System.out.print(rs.getString(1)+"  ");
            System.out.print(rs.getString(2)+"  ");
            System.out.print(rs.getString(3)+"  ");
            System.out.print(rs.getInt(4)+"  ");
            System.out.println(rs.getString(5));
        }
        //关闭连接
        con.close();
    }
    //查询所有学生
    public static void selectStu()  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String ss = "select * from why1";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            System.out.print(rs.getString(1)+"  ");
            System.out.print(rs.getString(2)+"  ");
            System.out.print(rs.getString(3)+"  ");
            System.out.print(rs.getInt(4)+"  ");
            System.out.println(rs.getString(5));
        }
        //关闭连接
        con.close();
    }
    //修改学生
    public static int  updateStu()  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String su = "update why1 set 姓名='小佳'where 姓名='小王'";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(su);
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        return i;
    }
    //修改学生，带有参数的
    public static int  updateStu(String oName,String nName)  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String su = "update why1 set 姓名=? where 姓名= ?";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(su);
        //执行前设置？
        ps.setString(2,oName);//设置第2个?
        ps.setString(1,nName);//设置第1个?
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        return i;
    }
    //删除学生
    public static int delStu(String name) throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String sd = "delete from why1 where 姓名=?";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(sd);
        ////执行前给?赋值
        ps.setString(1,name);
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        return i;
    }
    //删除学生,带有参数
    public static int delStu() throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String sd = "delete from why1 where 姓名='小王'";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(sd);
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        return i;
    }
    //添加学生
    public static void addStu() throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String si = "insert into why1 values('计算机',2022,'小王',20,'河南'),('会计',2022,'小刘',20,'江苏'),('体育',2023,'小琦',19,'新疆'),('美术',2023,'小敏',19,'重庆')";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(si);
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        System.out.println("插入了"+i+"行");
    }
    //添加学生，带参数
    public static void addStu(String department,String grade,String name,int age,String hometown) throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String si = "insert into why1 values(?,?,?,?,?)";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(si);
        //执行前，?有具体的值
        ps.setString(1,department);//设置第一个？的值
        ps.setString(2,grade);//设置第二个？的值
        ps.setString(3,name);//设置第三个？的值
        ps.setInt(4,age);//设置第四个？的值
        ps.setString(5,hometown);//设置第五个？的值
        //执行预处理语句,并将影响的行数保存到i中
        int i = ps.executeUpdate();
        //关闭连接
        con.close();
        System.out.println("插入了"+i+"行");
    }
    //批量添加学生，带参数
    public static void addStuBatch(String department,String grade,String name,int age,String hometown) throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //因为数据库表的名字叫why1，这个表有5列
        String si = "insert into why1 values(?,?,?,?,?)";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(si);
        //执行前，?有具体的值
        for (int i = 0; i < 10 ; i++) {
            ps.setString(1,department +i);//设置第一个？的值
            ps.setString(2,grade +i);//设置第二个？的值
            ps.setString(3,name +i);//设置第三个？的值
            ps.setInt(4,age+i);//设置第四个？的值
            ps.setString(5,hometown+i);//设置第五个？的值
            ps.addBatch();
        }
        //执行预处理语句,并将影响的行数保存到i中
        ps.executeBatch();
        //关闭连接
        con.close();
    }
    //获得数据库连接,无参
    public static  Connection getCon() throws ClassNotFoundException, SQLException {
        String className = "com.mysql.jdbc.Driver";
        String why1url = "jdbc:mysql://localhost:3306/xingong";
        //1.1加载驱动类
        Class.forName(className);
        //1.2获得链接并保存
        Connection con = DriverManager.getConnection(why1url, "root", "123456");
        return con;
    }
    //获得数据库连接,带一个参数
    public static  Connection getCon(String dbName) throws ClassNotFoundException, SQLException {
        String className = "com.mysql.jdbc.Driver";
        String why1url = "jdbc:mysql://localhost:3306/"+dbName;
        //1.1加载驱动类
        Class.forName(className);
        //1.2获得链接并保存
        Connection con = DriverManager.getConnection(why1url, "root", "123456");
        return con;
    }
    //查询所有学生到map,键值对
    public static Map<String,Student> selectStutoMap()  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //生成一个空的Map
        TreeMap<String,Student> students = new TreeMap();
        //因为数据库表的名字叫why1，这个表有5列
        String ss = "select * from why1";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            String dep = rs.getString(1);
            String gra = rs.getString(2);
            String name = rs.getString(3);
            int age = rs.getInt(4);
            String home = rs.getString(5);
            Student stu = new Student(dep,gra,name,age,home);//用查询出来的这一条记录的各个列的取值改造了一个对象
            students.put(name,stu);
        }
        //关闭连接
        con.close();
        return students;
    }
    //查询所有学生到Set
    public static Set<Student> selectStutoSet()  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //生成一个空的Set
        TreeSet<Student> students = new TreeSet<>();
        //因为数据库表的名字叫why1，这个表有5列
        String ss = "select * from why1";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            String dep = rs.getString(1);
            String gra = rs.getString(2);
            String name = rs.getString(3);
            int age = rs.getInt(4);
            String home = rs.getString(5);
            Student stu = new Student(dep,gra,name,age,home);//用查询出来的这一条记录的各个列的取值改造了一个对象
            students.add(stu);
        }
        //关闭连接
        con.close();
        return students;
    }
    //查询所有学生到list
    public static List<Student> selectStutoList()  throws ClassNotFoundException, SQLException {
        Connection con = getCon("xingong");
        //生成一个空的List
        ArrayList<Student> students = new ArrayList<>();
        //因为数据库表的名字叫why1，这个表有5列
        String ss = "select * from why1";
        //生成预处理语句
        PreparedStatement ps = con.prepareStatement(ss);
        //执行预处理语句,会有返回结果集
        ResultSet rs = ps.executeQuery();
        //rs,有next(),getXXx
        System.out.println("院部    年级  姓名  年龄  籍贯");
        while (rs.next()){//是否有下一个，同时指向下一个
            String dep = rs.getString(1);
            String gra = rs.getString(2);
            String name = rs.getString(3);
            int age = rs.getInt(4);
            String home = rs.getString(5);
            Student stu = new Student(dep,gra,name,age,home);//用查询出来的这一条记录的各个列的取值改造了一个对象
            students.add(stu);
        }
        //关闭连接
        con.close();
        return students;
    }
    public static void p2(Collection<Student> stus) {
        Iterator<Student> iter = stus.iterator();//获得迭代器
        while (iter.hasNext()) {//判断是否有下一个
            System.out.println(iter.next());//取下一个
        }
    }

    public static void p1(List<Student> stus) {
        for (Student student : stus) {
            System.out.println(student);
        }
    }
    public static void pmap(Map<String, Student> stus) {
        Set<String> keys = stus.keySet();//取出所有的键
        for (String key:keys) {
            System.out.println(key+"->"+ stus.get(key));
        }
    }
}
